<?php

/**
 * Description of Critere
 *
 * @author Jeremy
 * @version 1.0
 */
class Critere
{
    
    /**
     *
     * @param int $id Id d'un enseignant
     * @return mixed Array de critere
     */
    public function getListByEnseignant($id=0)
    {
        
        global $zones;
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT PromoCritere.IdPromo AS Id, Promo.NomPromo AS Nom, CritereEnseignantRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM PromoCritere, Promo, CritereEnseignantRS, TypeCritere
                    WHERE
                    CritereEnseignantRS.IdEnseignant = :id
                    AND
                    CritereEnseignantRS.IdTypeCritere = 1
                    AND
                    CritereEnseignantRS.IdTypeCritere = TypeCritere.IdTypeCritere
                    AND
                    CritereEnseignantRS.IdCritere = PromoCritere.IdPromoCritere
                    AND
                    PromoCritere.IdPromo = Promo.IdPromo
                    AND
                    PromoCritere.NbEtudiantMax > PromoCritere.NbEtudiant
                    UNION
                    SELECT CritereEnseignantRS.IdCritere as Id, \"\" AS Nom, CritereEnseignantRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM CritereEnseignantRS, TypeCritere
                    WHERE
                    CritereEnseignantRS.IdEnseignant = :id
                    AND
                    CritereEnseignantRS.IdTypeCritere = 2
                    AND
                    CritereEnseignantRS.IdTypeCritere = TypeCritere.IdTypeCritere
                    UNION
                    SELECT Technologie.IdTechnologie AS Id, Technologie.NomTechnologie AS Nom, CritereEnseignantRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM Technologie, CritereEnseignantRS, TypeCritere
                    WHERE CritereEnseignantRS.IdEnseignant = :id AND CritereEnseignantRS.IdTypeCritere = 3 AND CritereEnseignantRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereEnseignantRS.IdCritere = Technologie.IdTechnologie
                    UNION
                    SELECT Categorie.IdCategorie AS Id, Categorie.NomCategorie AS Nom, CritereEnseignantRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM Categorie, CritereEnseignantRS, TypeCritere
                    WHERE CritereEnseignantRS.IdEnseignant = :id AND CritereEnseignantRS.IdTypeCritere = 4 AND CritereEnseignantRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereEnseignantRS.IdCritere = Categorie.IdCategorie
                    UNION
                    SELECT DomaineApplication.IdDomaineApplication AS Id, DomaineApplication.NomDomaineApplication AS Nom, CritereEnseignantRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM DomaineApplication, CritereEnseignantRS, TypeCritere
                    WHERE CritereEnseignantRS.IdEnseignant = :id AND CritereEnseignantRS.IdTypeCritere = 5 AND CritereEnseignantRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereEnseignantRS.IdCritere = DomaineApplication.IdDomaineApplication
                    
                ";
        $c = $sql->prepare($query);

        // ID
        $c->bindParam(':id', $id, PDO::PARAM_INT);

        if ($c->execute())
        {

            $result = $c->fetchAll(PDO::FETCH_OBJ);

            $criteres = array();

            foreach ($result as $value)
            {
                if( $value->IdType == 2)
                {
                    switch($value->Id)
                    {
                        case 12:
                            $criteres[$value->IdType][$value->Id] = "Autre d&eacute;partement";
                            break;
                        case 13:
                            $criteres[$value->IdType][$value->Id] = "Etranger";
                            break;
                        default:
                            $criteres[$value->IdType][$value->Id] = $zones[$value->Id]['nom'];
                            break;
                    }
                }
                else
                {
                    $criteres[$value->IdType][$value->Id] = $value->Nom;
                }
            }

            return $criteres;
        }
        else
        {
            return array();
        }
    }

    /**
     *
     * @param int $id Id d'une offre
     * @return <type>
     */
    public function getListByOffre($id=0)
    {
        global $zones;
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT Promo.IdPromo AS Id, Promo.NomPromo AS Nom, CritereOffreRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM Promo, CritereOffreRS, TypeCritere
                    WHERE CritereOffreRS.IdOffre = :id AND CritereOffreRS.IdTypeCritere = 1 AND CritereOffreRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereOffreRS.IdCritere = Promo.IdPromo
                    UNION

                    SELECT Entreprise.IdZone AS Id, NULL AS Nom, '2' AS IdType, 'Zone' AS NomType
                    FROM ContactEntreprise, Entreprise, Offre
                    WHERE Offre.IdOffre = :id AND Offre.IdContactEntreprise = ContactEntreprise.idContactEntreprise AND ContactEntreprise.IdEntreprise = Entreprise.IdEntreprise 
                    UNION

                    SELECT Technologie.IdTechnologie AS Id, Technologie.NomTechnologie AS Nom, CritereOffreRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM Technologie, CritereOffreRS, TypeCritere
                    WHERE CritereOffreRS.IdOffre = :id AND CritereOffreRS.IdTypeCritere = 3 AND CritereOffreRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereOffreRS.IdCritere = Technologie.IdTechnologie
                    UNION

                    SELECT Categorie.IdCategorie AS Id, Categorie.NomCategorie AS Nom, CritereOffreRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM Categorie, CritereOffreRS, TypeCritere
                    WHERE CritereOffreRS.IdOffre = :id AND CritereOffreRS.IdTypeCritere = 4 AND CritereOffreRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereOffreRS.IdCritere = Categorie.IdCategorie
                    UNION

                    SELECT DomaineApplication.IdDomaineApplication AS Id, DomaineApplication.NomDomaineApplication AS Nom, CritereOffreRS.IdTypeCritere AS IdType, TypeCritere.NomTypeCritere AS NomType
                    FROM DomaineApplication, CritereOffreRS, TypeCritere
                    WHERE CritereOffreRS.IdOffre = :id AND CritereOffreRS.IdTypeCritere = 5 AND CritereOffreRS.IdTypeCritere = TypeCritere.IdTypeCritere AND CritereOffreRS.IdCritere = DomaineApplication.IdDomaineApplication

                    ";
        $c = $sql->prepare($query);

        // ID
        $c->bindParam(':id', $id, PDO::PARAM_INT);

        if ($c->execute())
        {

            $result = $c->fetchAll(PDO::FETCH_OBJ);

            $criteres = array();

            foreach ($result as $value)
            {
                if( $value->IdType == 2)
                {
                    switch($value->Id)
                    {
                        case 12:
                            $criteres[$value->IdType][$value->Id] = "Autre d&eacute;partement";
                            break;
                        case 13:
                            $criteres[$value->IdType][$value->Id] = "Etranger";
                            break;
                        default:
                            $criteres[$value->IdType][$value->Id] = $zones[$value->Id]['nom'];
                            break;
                    }
                }
                else
                {
                    $criteres[$value->IdType][$value->Id] = $value->Nom;
                }
            }

            return $criteres;
        }
        else
        {
            return array();
        }
    }

    /**
     * @see Technologie
     * @return mixed Array de Technologie
     */
    public function getListTechnologies()
    {
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT *
                    FROM Technologie
                ";
        $c = $sql->query($query);
        $result = $c->fetchAll(PDO::FETCH_OBJ);

        if (!empty($result))
        {
            return $result;
        }
        else
        {
            return array();
        }
    }

    /**
     *
     * @see DomaineApplication
     * @return mixed Array de Domaine
     */
    public function getListDomaines()
    {
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT *
                    FROM DomaineApplication
                ";
        $c = $sql->query($query);
        $result = $c->fetchAll(PDO::FETCH_OBJ);

        if (!empty($result))
        {
            return $result;
        }
        else
        {
            return array();
        }
    }

    /**
     * @see Categorie
     * @return mixed Array de Categorie
     */
    public function getListCategories()
    {
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT *
                    FROM Categorie
                ";
        $c = $sql->query($query);
        $result = $c->fetchAll(PDO::FETCH_OBJ);

        if (!empty($result))
        {
            return $result;
        }
        else
        {
            return array();
        }
    }

    /**
     *
     * @return int Nombre de type de critere
     */
    public function countTypes()
    {
        $sql = SQL::getInstance()->getConnection();

        $query = "
                    SELECT COUNT(IdTypeCritere) as count
                    FROM TypeCritere
                ";
        $c = $sql->query($query);
        $result = $c->fetch(PDO::FETCH_OBJ);

        return $result->count;
    }

}

?>
